package promiscuous_genes;


require Exporter;


our @ISA = qw(Exporter);
our $VERSION = 1.00;
our @EXPORT = qw(get_promiscuous_genes);

use strict;
use DBI;
use DBD::Pg;
use Carp;
use warnings;
use Data::Dump qw(dump);




sub get_promiscuous_genes($$\%$$)
{
	my ($dbh, $protocol_id, $promiscuous_genes, $ortho_name1, $ortho_name2) = @_;
	print STDERR "\t\tGet gene fragments (<3 exons) from promiscuous gene families\n";
	print STDERR "\t\t\t    Get orthologues on > 4 chromosomes\n";
	my $sql_cmd = <<"PL/SQLCMD";
	-- CREATE TABLE with > 4 canonical chromosomes
	CREATE TEMP TABLE t_promiscuous_orthologs (ortholog_id INTEGER);
	INSERT 
		INTO t_promiscuous_orthologs 
			SELECT 
					ortholog_id
				FROM 
					orthologs.ortholog_sets os NATURAL JOIN 
					taxon.gene_loci 
				WHERE 
					protocol_id = $protocol_id AND
					chromosome ~ '^[A-Z0-9]{1,4}\$'
				GROUP BY ortholog_id, species
					HAVING COUNT(distinct chromosome) > 5;
PL/SQLCMD
    $dbh->do($sql_cmd) or die $dbh->errstr.".";
	#print $sql_cmd;




	print STDERR "\t\t\t    Get the archetypal genes in promiscuous clades (with matching boundaries, exons > 3)\n";
	$sql_cmd = <<"PL/SQLCMD";
	-- Get the archetypal genes in promiscuous clades (with matching boundaries, exons > 3)
	CREATE TEMP TABLE t_canonical_genes (gene_id text, ortholog_id INTEGER);
	CREATE TEMP TABLE t1 (gene_id1 text, gene_id2 text, ortholog_id INTEGER);
	INSERT 
		INTO t1 
				(gene_id1, gene_id2, ortholog_id)
		SELECT 
				gene_id1,
				gene_id2,
				ortholog_id
			FROM 
				orthologs.ortholog_sets os1 JOIN 
				orthologs.ortholog_sets os2 USING (protocol_id, ortholog_id) JOIN
				t_promiscuous_orthologs USING (ortholog_id),
				orthologs.ortholog_exons oe, 
				taxon.prot_exon_stats pes1, 
				taxon.prot_exon_stats pes2 
			WHERE 
				os1.species = '$ortho_name1' AND 
				os2.species = '$ortho_name2' AND 
				oe.gene_id1 = os1.gene_id AND 
				oe.gene_id2 = os2.gene_id AND 
				protocol_id = $protocol_id AND 
				pes1.real_exon_count >= 3 AND 
				pes2.real_exon_count >= 3 AND 
				pes1.prot_id = os1.prot_id AND 
				pes2.prot_id = os2.prot_id;
	INSERT 
		INTO t_canonical_genes(gene_id, ortholog_id)
		SELECT
				gene_id1, ortholog_id
			FROM
				t1
		UNION
		SELECT
				gene_id2, ortholog_id
			FROM
				t1;
PL/SQLCMD
    $dbh->do($sql_cmd) or die $dbh->errstr.".";
	#print $sql_cmd;




	print STDERR "\t\t\t    Get list of orthologue clades with archetypal matching (real) genes\n";
	$sql_cmd = <<"PL/SQLCMD";
	-- Get list of orthologue clades with archetypal matching (real) genes
	CREATE TEMP TABLE t_ortholog_sets_with_canonical_genes (ortholog_id INTEGER);
	INSERT 
		INTO t_ortholog_sets_with_canonical_genes 
		SELECT 
				DISTINCT ON (ortholog_id)
				ortholog_id
			FROM 
				t_canonical_genes;
PL/SQLCMD
    $dbh->do($sql_cmd) or die $dbh->errstr.".";
	#print $sql_cmd;




	print STDERR "\t\t\t    Get list of genes matching archetypes\n";
	$sql_cmd = <<"PL/SQLCMD";
	-- Get list of real genes matching archetypes
	CREATE TEMP TABLE t_non_pseudo_genes (gene_id text);
	INSERT INTO t_non_pseudo_genes 
		SELECT 
				DISTINCT ON (gene_id1)
				gene_id1 
			FROM 
				orthologs.ortholog_sets os1  NATURAL JOIN
				t_ortholog_sets_with_canonical_genes JOIN 
				orthologs.ortholog_sets os2 USING (protocol_id, ortholog_id),
				orthologs.ortholog_exons oe JOIN
				t_canonical_genes ON (gene_id2 = gene_id)
			WHERE
				os1.species = '$ortho_name1' AND
				os2.species = '$ortho_name2' AND
				oe.gene_id1 = os1.gene_id AND
				oe.gene_id2 = os2.gene_id AND
				protocol_id = $protocol_id;
	INSERT INTO t_non_pseudo_genes 
		SELECT 
				DISTINCT ON (gene_id2)
				gene_id2 
			FROM 
				orthologs.ortholog_sets os1  NATURAL JOIN
				t_ortholog_sets_with_canonical_genes JOIN 
				orthologs.ortholog_sets os2 USING (protocol_id, ortholog_id), 
				orthologs.ortholog_exons oe JOIN
				t_canonical_genes ON (gene_id1 = gene_id)
			WHERE
				os1.species = '$ortho_name1' AND
				os2.species = '$ortho_name2' AND
				oe.gene_id1 = os1.gene_id AND
				oe.gene_id2 = os2.gene_id AND
				protocol_id = $protocol_id;
PL/SQLCMD
    $dbh->do($sql_cmd) or die $dbh->errstr.".";
	#print $sql_cmd;




	print STDERR "\t\t\t    Keep genes from odd chromosomes, or with > 3 exons\n";
	$sql_cmd = <<"PL/SQLCMD";
	--Keep genes from odd chromosomes, or with > 3 exons
	INSERT INTO t_non_pseudo_genes
			SELECT 
					gene_id
				FROM 
					orthologs.ortholog_sets os NATURAL JOIN 
					taxon.gene_loci  NATURAL JOIN
					t_ortholog_sets_with_canonical_genes
				WHERE 
					protocol_id = $protocol_id AND
					chromosome !~ '^[A-Z0-9]{1,4}\$';

	--add to real genes current real genes with > 3 exons
	INSERT INTO t_non_pseudo_genes
		SELECT 
				gene_id
			FROM 
				orthologs.ortholog_sets os NATURAL JOIN
				taxon.prot_exon_stats pes NATURAL JOIN
				t_ortholog_sets_with_canonical_genes
			WHERE 
				protocol_id = $protocol_id AND
				pes.real_exon_count > 3;
PL/SQLCMD
    $dbh->do($sql_cmd) or die $dbh->errstr.".";
	#print $sql_cmd;




	print STDERR "\t\t\t    Any gene in a promiscuous clade not in the above criteria real gene is a pseudo\n";
	for my $species($ortho_name1, $ortho_name2)
	{
		$sql_cmd = <<"PL/SQLCMD";
        SELECT 
                DISTINCT ON (os.disentangled_gene_id)
                os.disentangled_gene_id 
            FROM 
                orthologs.ortholog_sets os NATURAL JOIN
                t_ortholog_sets_with_canonical_genes LEFT JOIN 
                t_non_pseudo_genes USING (gene_id) 
            WHERE 
                protocol_id = $protocol_id AND
                t_non_pseudo_genes.gene_id IS NULL AND
                os.species = '$species';
PL/SQLCMD
		my $data = $dbh->selectall_arrayref($sql_cmd) or die;
		print STDERR "\t\t\t", scalar @$data, " promiscuous gene fragments from $species.\n";
		++$promiscuous_genes->{$_->[0]} for (@$data);
	}


	#
	#	cleanup
	#
	$sql_cmd = <<"PL/SQLCMD";
    DROP TABLE t_promiscuous_orthologs;
    DROP TABLE t_canonical_genes;
    DROP TABLE t1;
    DROP TABLE t_ortholog_sets_with_canonical_genes;
    DROP TABLE t_non_pseudo_genes;
PL/SQLCMD
$dbh->do($sql_cmd) or die $dbh->errstr.".";

}






1;
